package com.dctp.ems.controller.chart;

import com.dctp.common.cache.UserCache;
import com.dctp.common.controller.BaseController;
import com.dctp.common.db.FxRateDb;
import com.dctp.common.utils.CollectUtil;
import com.dctp.common.utils.StringUtil;
import com.dctp.common.vo.JsonResult;
import com.dctp.common.vo.PageResult;
import com.jfinal.core.NotAction;
import com.jfinal.core.Path;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

@Path("/homeStat")
public class HomeStatController extends BaseController  {

    private static final Logger logger = LoggerFactory.getLogger(HomeStatController.class);
    public void trendData() {
        String chartKey = getPara("trendType"); // 如"2"
        Integer days = getParaToInt("days", 7); // 默认为7天
        ChartQueryManager.ChartQuery config = ChartQueryManager.get(chartKey);
        if (config == null) {
            renderJson(JsonResult.fail("配置不存在"));
            return;
        }

        String filterField = getFilterField();
        logger.info("query Field :{}", filterField);
        Object filterFieldValue = filterField != null ? getRequestUser().getLong("user_id") : null;
        String symbol = config.symbolField != null ? getPara("symbol") : null;

        // 构建SQL
        StringBuilder sql = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sql.append("SELECT DATE_FORMAT(").append(config.dateField).append(", '%m-%d') as date, ")
                .append(config.statField).append(" as value FROM ").append(config.tableName)
                .append(" WHERE ").append(config.dateField).append(" >= ? ");
        params.add(LocalDate.now().minusDays(days - 1));
        if (config.filterField != null && config.filterValue != null) {
            sql.append(" AND ").append(config.filterField).append(" = ?");
            params.add(config.filterValue);
        }
        if (filterField != null) {
            sql.append(" AND ").append(filterField).append(" = ?");
            params.add(filterFieldValue);
        }





        if (config.symbolField != null && symbol != null && !symbol.isEmpty()) {
            sql.append(" AND ").append(config.symbolField).append(" = ?");
            params.add(symbol);
        }
        sql.append(" GROUP BY date ORDER BY date");

        List<com.jfinal.plugin.activerecord.Record> list = Db.find(sql.toString(), params.toArray());
        Map<String, BigDecimal> dayMap = new HashMap<>();
        for (Record r : list) {
            BigDecimal value = r.getBigDecimal("value");
            dayMap.put(r.getStr("date"), value == null ? BigDecimal.ZERO : value);
        }

        List<Map<String, Object>> result = new ArrayList<>();
        LocalDate today = LocalDate.now();
        for (int i = days - 1; i >= 0; i--) {
            LocalDate d = today.minusDays(i);
            String dateStr = d.format(DateTimeFormatter.ofPattern("MM-dd"));
            BigDecimal value = dayMap.getOrDefault(dateStr, BigDecimal.ZERO);
            result.add(Map.of("date", dateStr, "value", value));
        }
        Map<String, Object> resultMap = getTotalStat();
        resultMap.put("data", result);
        Integer loadStat = getParaToInt("loadStat", 1);
        if (loadStat == 1) {
            resultMap.putAll(getTotalStat());
        }
        renderJson(JsonResult.successWithData(resultMap));
    }

    @NotAction
    private Map<String,Object> getTotalStat() {
        String chartKey = getPara("trendType");
        ChartQueryManager.ChartQuery config = ChartQueryManager.get(chartKey);
        if (config == null) {
            renderJson(JsonResult.fail("配置不存在"));
            return null;
        }

        if ("1".equals(chartKey)) {
            logger.info("");
        }

        String filterField = getFilterField();
        Object filterFieldValue = filterField != null ? getRequestUser().getLong("user_id") : null;
        String symbol = config.symbolField != null ? getPara("symbol") : null;

        // 总计
        StringBuilder totalSql = new StringBuilder("SELECT IFNULL(")
                .append(config.statField.replaceAll("(?i)AS.*", ""))
                .append(",0) FROM ").append(config.tableName).append(" WHERE 1=1");
        List<Object> totalParams = new ArrayList<>();
        if (config.filterField != null && config.filterValue != null) {
            if ("8".equals(chartKey)) {
                totalSql.append(" AND status in (1,2) ");
            }else {
                totalSql.append(" AND ").append(config.filterField).append(" = ?");
                totalParams.add(config.filterValue);
            }
        }
        if (filterField != null) {
            totalSql.append(" AND ").append(filterField).append(" = ?");
            totalParams.add(filterFieldValue);
        }
        if (config.symbolField != null && symbol != null && !symbol.isEmpty()) {
            totalSql.append(" AND ").append(config.symbolField).append(" = ?");
            totalParams.add(symbol);
        }
        BigDecimal total = Db.queryBigDecimal(totalSql.toString(), totalParams.toArray());

        // 今日与昨日同一时刻
        LocalDate today = LocalDate.now();
        LocalDateTime todayStart = today.atStartOfDay();
        LocalDateTime now = LocalDateTime.now();

        StringBuilder todaySumSql = new StringBuilder("SELECT IFNULL(")
                .append(config.statField.replaceAll("(?i)AS.*", ""))
                .append(",0) FROM ").append(config.tableName)
                .append(" WHERE ").append(config.dateField).append(" >= ? AND ").append(config.dateField).append(" < ?");
        List<Object> todaySumParams = new ArrayList<>(Arrays.asList(todayStart, now));
        if (config.filterField != null && config.filterValue != null) {
            if ("8".equals(chartKey)) {
                todaySumSql.append(" AND status in (1,2) ");
            }else {
                todaySumSql.append(" AND ").append(config.filterField).append(" = ?");
                todaySumParams.add(config.filterValue);
            }
        }
        if (filterField != null) {
            todaySumSql.append(" AND ").append(filterField).append(" = ?");
            todaySumParams.add(filterFieldValue);
        }
        if (config.symbolField != null && symbol != null && !symbol.isEmpty()) {
            todaySumSql.append(" AND ").append(config.symbolField).append(" = ?");
            todaySumParams.add(symbol);
        }
        BigDecimal todaySum = Db.queryBigDecimal(todaySumSql.toString(), todaySumParams.toArray());

        LocalDateTime yesterdayStart = todayStart.minusDays(1);
        LocalDateTime yesterdayNow = yesterdayStart.plusHours(now.getHour())
                .plusMinutes(now.getMinute()).plusSeconds(now.getSecond());
        StringBuilder yesterdaySumSql = new StringBuilder("SELECT IFNULL(")
                .append(config.statField.replaceAll("(?i)AS.*", ""))
                .append(",0) FROM ").append(config.tableName)
                .append(" WHERE ").append(config.dateField).append(" >= ? AND ").append(config.dateField).append(" < ?");
        List<Object> yesterdaySumParams = new ArrayList<>(Arrays.asList(yesterdayStart, yesterdayNow));
        if (config.filterField != null && config.filterValue != null) {
            if ("8".equals(chartKey)) {
                yesterdaySumSql.append(" AND status in (1,2) ");
            }else {
                yesterdaySumSql.append(" AND ").append(config.filterField).append(" = ?");
                yesterdaySumParams.add(config.filterValue);
            }
        }
        if (filterField != null) {
            yesterdaySumSql.append(" AND ").append(filterField).append(" = ?");
            yesterdaySumParams.add(filterFieldValue);
        }
        if (config.symbolField != null && symbol != null && !symbol.isEmpty()) {
            yesterdaySumSql.append(" AND ").append(config.symbolField).append(" = ?");
            yesterdaySumParams.add(symbol);
        }
        BigDecimal yesterdaySum = Db.queryBigDecimal(yesterdaySumSql.toString(), yesterdaySumParams.toArray());

        BigDecimal percent = BigDecimal.ZERO;
        if (yesterdaySum.compareTo(BigDecimal.ZERO) > 0) {
            percent = todaySum.multiply(BigDecimal.valueOf(100))
                    .divide(yesterdaySum, 2, RoundingMode.HALF_UP);
        }

        Map<String, Object> resp = new HashMap<>();
        //用户人数统计，去掉1
        if ("1".equals(chartKey) && total.compareTo(BigDecimal.ZERO) > 0) {
            total = total.subtract(BigDecimal.ONE);
        }
        resp.put("total", total);
        resp.put("percent", percent);
        return resp;
    }

    public void totalStat() {
        Map<String,Object> result = getTotalStat();
        renderJson(JsonResult.successWithData(result));
    }


    public void roleStat() {

        Record admin = getRequestUser();
        String groupByKey = getGroupByIdKey();
        int page = getInt("currentPage", 1);
        int pageSize = getInt("pageSize", 10);

        StringBuilder sql = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sql.append(" from dctp_user ");
        String queryFilter = getFilterField();
        if (StringUtil.isNotEmpty(queryFilter)) {
            sql.append(" where ").append(queryFilter).append(" = ? ");
            params.add(admin.getLong("user_id"));
        }

        // 过滤掉 groupByKey 为 null
        if (sql.toString().contains("where")) {
            sql.append(" and ").append(groupByKey).append(" is not null ");
        } else {
            sql.append(" where ").append(groupByKey).append(" is not null ");
        }

        sql.append(" group by ").append(groupByKey).append(" order by ").append(groupByKey).append(" desc ");

        String selectSql = "select " + groupByKey + ", count(*) as userCount";
        System.out.println(selectSql + " " + sql);
        // 分页，查出每个groupByKey及人数
        var p = Db.paginate(page, pageSize,
                selectSql,
                sql.toString(), params.toArray());
        List<Record> groupList = p.getList();
        if (CollectUtil.isEmpty(groupList)) {
            renderJson(JsonResult.successWithData(new PageResult<>(p.getList(), p.getTotalRow(), pageSize, page))); // 直接返回空
            return;
        }

        List<Long> groupIds = groupList.stream()
                .map(r -> r.getLong(groupByKey))
                .toList();
        if (groupIds.isEmpty()) {
            renderJson(p);
            return;
        }
        String inSql = groupIds.stream().map(String::valueOf).collect(Collectors.joining(","));


        Record rateRecord = FxRateDb.findByCodeAndTarget("USDT","HKDT");
        BigDecimal rate = new BigDecimal(75);
        if (null != rateRecord) {
            rate = rateRecord.getBigDecimal("rate");
        }

        // 1. 充值
        Map<Long, BigDecimal> rechargeMap = Db.find(
                        "select " + groupByKey + " as gid, IFNULL(SUM(amount * "+rate+"),0) as total from dctp_recharge where " +
                                groupByKey + " in (" + inSql + ") and audit_status=1 group by " + groupByKey)
                .stream().collect(Collectors.toMap(r -> r.getLong("gid"), r -> r.getBigDecimal("total")));

        // 2. 转账
        Map<Long, BigDecimal> transferMap = Db.find(
                        "select " + groupByKey + " as gid, IFNULL(SUM(amount),0) as total from dctp_transfer where status = 1 and " +
                                groupByKey + " in (" + inSql + ") group by " + groupByKey)
                .stream().collect(Collectors.toMap(r -> r.getLong("gid"), r -> r.getBigDecimal("total")));

        // 3. 提现
        Map<Long, BigDecimal> withdrawMap = Db.find(
                        "select " + groupByKey + " as gid, IFNULL(SUM(amount_from),0) as total from dctp_withdraw where audit_status = 1 and " +
                                groupByKey + " in (" + inSql + ") group by " + groupByKey)
                .stream().collect(Collectors.toMap(r -> r.getLong("gid"), r -> r.getBigDecimal("total")));

        // 4. 佣金
        Map<Long, BigDecimal> commissionMap = Db.find(
                        "select " + groupByKey + " as gid, IFNULL(SUM(fr_amount),0) as total from dctp_commission_record where " +
                                groupByKey + " in (" + inSql + ") group by " + groupByKey)
                .stream().collect(Collectors.toMap(r -> r.getLong("gid"), r -> r.getBigDecimal("total")));

        //持仓
        //staus = 3撤销状态
        String orderSQL = "SELECT " + groupByKey + " as gid, "
                + "SUM(CASE WHEN status = 2 THEN realized_pnl ELSE 0 END) as total_pnl, " // 平仓盈亏
                + "COUNT(DISTINCT CASE WHEN status != 3 THEN user_id ELSE NULL END) as order_count, " // ✅ 下单人数
                + "SUM(CASE WHEN status != 3 THEN quantity ELSE 0 END) as order_volume, " // 下单手数
                + "SUM(CASE WHEN status = 1 THEN margin_occupied ELSE 0 END) as margin, " // 当前持仓保证金
                + "SUM(CASE WHEN status != 3 THEN open_service_fee + close_service_fee ELSE 0 END) as service_fee " // 服务费
                + "FROM dctp_order WHERE " + groupByKey + " IN (" + inSql + ") GROUP BY " + groupByKey;



        // 查询dctp_order多字段统计，一次查出
        Map<Long, Record> orderStatMap = Db.find(orderSQL).stream()
                .collect(Collectors.toMap(
                        r -> r.getLong("gid"), // 分组字段
                        r -> r
                ));

        // 合并所有业绩统计到分页结果
        for (Record group : groupList) {
            Long gid = group.getLong(groupByKey);
            Record showInfo = UserCache.getShowInfo(gid);
            group.set("user",showInfo);
            group.set("totalRecharge", rechargeMap.getOrDefault(gid, BigDecimal.ZERO));
            group.set("totalTransfer", transferMap.getOrDefault(gid, BigDecimal.ZERO));
            group.set("totalWithdraw", withdrawMap.getOrDefault(gid, BigDecimal.ZERO));
            group.set("totalCommission", commissionMap.getOrDefault(gid, BigDecimal.ZERO));

            Record orderStat = orderStatMap.get(gid);
            if (orderStat != null) {
                group.set("totalPnl", orderStat.get("total_pnl"));
                group.set("orderCount", orderStat.get("order_count"));
                group.set("orderVolume", orderStat.get("order_volume"));
                group.set("margin", orderStat.get("margin"));
                group.set("serviceFee", orderStat.get("service_fee"));
            } else {
                // 没有数据补0
                group.set("totalPnl", BigDecimal.ZERO);
                group.set("orderCount", 0L);
                group.set("orderVolume", BigDecimal.ZERO);
                group.set("margin", BigDecimal.ZERO);
                group.set("serviceFee", BigDecimal.ZERO);
            }
        }

        renderJson(JsonResult.successWithData(
                new PageResult<>(p.getList(), p.getTotalRow(), pageSize, page)
        ));
    }
}
